﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Citi.HeadCount.Domain;
using Citi.HeadCount.WebUI.Models;
using System.Data;
using System.IO;
using System.Reflection;
using System.Collections;
using Citi.HeadCount.Domain.Repository;
using System.Text;

namespace Citi.HeadCount.WebUI.Util
{
    public class ChildrenInformation
    {
        public string Name { get; set; }
        public IList Items { get; set; }
    }

    public class ImportHelper
    {
        public List<ChildrenInformation> Children = new List<ChildrenInformation>();

        #region Importing DSTM process

        public List<GOC> ImportGOCData(string pathFile, ref List<ErrorModel> errors)
        {
            var gocs = ImportFile<GOC>(new ColumnMappingGOC(), pathFile, "GOC Attributes", ref errors);

            gocs.ForEach(x => { x.LastUpdate = DateTime.Now; });

            SaveCountry(gocs);
            SaveFRSBu(gocs);
            SaveFRSOu(gocs);
            SaveFunction(gocs);
            SaveManagedSegment(gocs);
            SaveLevel(gocs);
            SaveSID(gocs);

            new RepositoryBase<GOC>().DeleteByReferenceDate(gocs.First().ReferenceDate);

            SaveAll(gocs);
            return gocs;
        }

        public List<GOC> ImportGOCCode(string pathFile, ref List<ErrorModel> errors)
        {
            var gocsCode = ImportFile<GOC>(new ColumnMappingGOCCode(), pathFile, "GOC Codes", ref errors);
            var clauses = gocsCode.Select(x => string.Format("UPDATE GOC SET ExpenseCode='{0}', CorpCode = '{1}' WHERE GOCAttribute = '{2}'", x.ExpenseCode, x.CorpCode, x.GOCAttribute));

            new Domain.Repository.RepositoryBase<GOC>().ExecuteQuery(clauses);
            return gocsCode;
        }

        private void SaveCountry(List<GOC> gocs)
        {
            gocs.ForEach(x => x.CountryId = 1);
        }

        private void SaveSID(List<GOC> gocs)
        {
            var sids = gocs.Select(x => x.Sid).Distinct(new SIDComparer()).ToList();
            sids.ForEach(x => x.LastUpdate = DateTime.Now);
            SaveAll(sids);
            gocs.ForEach(x => { x.SidId = sids.First(y => y.Description == x.Sid.Description).Id; });
        }

        private void SaveLevel(List<GOC> gocs)
        {
            var levels = gocs.Select(x => x.Level).Distinct(new LevelComparer()).ToList();
            levels.ForEach(x => x.LastUpdate = DateTime.Now);
            SaveAll(levels);
            gocs.ForEach(x => { x.LevelId = levels.First(y => y.Code == x.Level.Code).Id; });
        }

        private void SaveFunction(List<GOC> gocs)
        {
            var functions = new RepositoryBase<Function>().GetAll();
            gocs.ForEach(x => x.FunctionId = functions.FirstOrDefault(y=> y.Code == x.Function.Code).Id);
        }

        private void SaveManagedSegment(List<GOC> gocs)
        {
            var segments = new RepositoryBase<ManagedSegment>().GetAll();
            gocs.ForEach(x => x.ManagedSegmentId = segments.FirstOrDefault(y => y.Code == x.ManagedSegment.Code).Id);
        }

        private void SaveFRSOu(List<GOC> gocs)
        {
            var frsous = gocs.Select(x => x.FRSOu).Distinct(new FRSOuComparer()).ToList();
            frsous.ForEach(x => x.LastUpdate = DateTime.Now);
            SaveAll(frsous);
            gocs.ForEach(x => { x.FRSOuId = frsous.First(y => y.Description == x.FRSOu.Description).Id; });
        }

        private void SaveFRSBu(List<GOC> gocs)
        {
            var frsbus = gocs.Select(x => x.FRSBu).Distinct(new FRSBuComparer()).ToList();
            frsbus.ForEach(x => x.LastUpdate = DateTime.Now);
            SaveAll(frsbus);
            gocs.ForEach(x => { x.FRSBuId = frsbus.First(y => y.Description == x.FRSBu.Description).Id; });
        }

        #endregion

        #region Importing Managed Segment

        public List<ManagedSegment> ImportManagedSegmentData(string filePath, ref List<ErrorModel> errors)
        {
            var segments = ImportFile<ManagedSegment>(new ColumnMappingManagedSegment(), filePath, "Managed Segments", ref errors, null, true);

            segments = segments.OrderBy(x => x.TreeLevel).ThenBy(x => x.ParentId).ToList();

            //segments.ForEach(x => { x.LastUpdate = DateTime.Now; x.ParentId = x.ParentId == x.Id ? null : x.ParentId; });

            //SaveAll(segments);

            segments.ForEach(x => { x.LastUpdate = DateTime.Now; x.ParentCode = x.ParentCode == x.Code ? null : x.ParentCode; });

            SaveAll(segments);

            var databaseSegments = new RepositoryBase<ManagedSegment>().GetAll();
            var pathDate = GetDateFile(filePath);

            segments.ForEach(x =>
            {
                var database = databaseSegments.Where(y => y.Code == x.ParentCode && y.ReferenceDate <= pathDate).OrderByDescending(y => y.ReferenceDate).FirstOrDefault();

                if (database != null)
                    x.ParentId = database.Id;
            });

            SaveAll(segments);
            return segments;
        }

        #endregion

        #region Importing Function

        public List<Function> ImportFunctionData(string filePath, ref List<ErrorModel> errors)
        {
            var functions = ImportFile<Function>(new ColumnMappingFunction(), filePath, "Functions", ref errors, null, true);

            functions = functions.OrderBy(x => x.TreeLevel).ThenBy(x => x.ParentId).ToList();

            functions.ForEach(x => { x.LastUpdate = DateTime.Now; x.ParentCode = x.ParentCode == x.Code ? null : x.ParentCode; });

            SaveAll(functions);

            var databaseFunctions = new RepositoryBase<Function>().GetAll();
            var pathDate = GetDateFile(filePath);

            functions.ForEach(x =>
            {
                var database = databaseFunctions.Where(y => y.Code == x.ParentCode && y.ReferenceDate <= pathDate).OrderByDescending(y => y.ReferenceDate).FirstOrDefault();

                if (database != null)
                    x.ParentId = database.Id;
            });

            SaveAll(functions);

            return functions;
        }

        #endregion

        #region Generic Import

        private void SaveAll<T>(IEnumerable<T> objects) where T : EntityBase
        {
            new Domain.Repository.RepositoryBase<T>().Save(objects.Distinct());
        }

        private List<T> ImportFile<T>(ColumnMapping mappingColumn, string pathFile, string fileDescription, ref List<ErrorModel> errors, string worksheetName = null, bool updateReferenceDate = false) where T : EntityBase
        {
            DateTime pathDate;
            if (ValidateFile(pathFile, fileDescription, errors, out pathDate)) return null;

            using (var reader = new ExcelReader(pathFile, true, true))
            {
                var dataTable = worksheetName == null ? reader.GetData() : reader.GetData(worksheetName);
                var rows = dataTable.Rows.Cast<DataRow>().ToList();
                var lines = new List<T>();
                var blankLine = 0;

                foreach (var row in rows)
                {
                    if (row.ItemArray.Count(x => string.IsNullOrEmpty(x.ToString())) == row.ItemArray.Count())
                    {
                        blankLine++;
                        if (blankLine >= 5) break;
                        continue;
                    }

                    var line = rows.IndexOf(row);

                    ErrorModel error;

                    var objectLine = AddLine<T>(mappingColumn, row, pathDate, out error);

                    if (objectLine == null)
                    {
                        error.Line = line;
                        error.File = fileDescription;
                        errors.Add(error);
                        continue;
                    }

                    if (updateReferenceDate)
                    {
                        var property = GetPropertyCodeOrDescription(objectLine.GetType());
                        var databaseObject = GetItemFromChildren(property.GetValue(objectLine, null).ToString(), objectLine.GetType());

                        if (databaseObject != null)
                        {
                            if (databaseObject.IsSameInformation(objectLine)) continue;

                            objectLine.UpdateDates(pathDate);
                            lines.Add(objectLine);
                        }
                        else
                            lines.Add(objectLine);
                    }
                    else
                        lines.Add(objectLine);
                }

                return lines;
            }
        }


        private static bool ValidateFile(string pathFile, string fileDescription, List<ErrorModel> errors, out DateTime pathDate)
        {
            pathDate = DateTime.MinValue;

            if (!File.Exists(pathFile))
            {
                errors.Add(new ErrorModel(0, string.Format(Message.FileNotExists, pathFile), fileDescription));
                return true;
            }

            if (Path.GetExtension(pathFile) != ".xls" && Path.GetExtension(pathFile) != ".xlsx")
            {
                errors.Add(new ErrorModel(0, string.Format(Message.InvalidFileExtension, pathFile), fileDescription));
                return true;
            }

            pathDate = GetDateFile(pathFile);

            if (pathDate == DateTime.MinValue)
            {
                errors.Add(new ErrorModel(0, string.Format(Message.InvalidFileName, fileDescription, pathFile), fileDescription));
                return true;
            }

            return false;
        }

        private T AddLine<T>(ColumnMapping mappingColumn, DataRow row, DateTime pathDate, out ErrorModel errorModel) where T : EntityBase
        {
            errorModel = new ErrorModel();

            var objectLine = (T)Activator.CreateInstance(typeof(T));

            try
            {
                foreach (PropertyInfo propertyInfo in mappingColumn.GetType().GetProperties())
                {
                    var columnIndex = int.Parse(propertyInfo.GetValue(mappingColumn, null).ToString());

                    if (columnIndex == -1) continue;

                    var value = row[columnIndex].ToString();

                    if (string.IsNullOrEmpty(value)) continue;

                    var objectProperty = objectLine.GetType().GetProperty(propertyInfo.Name);
                    if (objectProperty == null) continue;

                    if (objectProperty.PropertyType.Namespace.StartsWith("Citi.HeadCount.Domain"))
                    {
                        var child = GetDomainValue(value, objectProperty, row, mappingColumn, pathDate);
                        objectProperty.SetValue(objectLine, child, null);
                    }
                    else
                    {
                        if (objectProperty.PropertyType.IsGenericType && objectProperty.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        {
                            objectProperty.SetValue(objectLine,
                                string.IsNullOrEmpty(value)
                                    ? null
                                    : Convert.ChangeType(value, objectProperty.PropertyType.GetGenericArguments()[0]), null);
                        }
                        else
                            objectProperty.SetValue(objectLine, Convert.ChangeType(value, objectProperty.PropertyType), null);
                    }
                }

                FillDate("ReferenceDate", objectLine, pathDate);
                FillDate("LastUpdate", objectLine, DateTime.Now);

                return objectLine;
            }
            catch (Exception e)
            {
                errorModel = new ErrorModel(0, e.Message, string.Empty);
                return default(T);
            }
        }

        private object GetDomainValue(string value, PropertyInfo objectProperty, DataRow row, ColumnMapping mappingColumn, DateTime pathDate)
        {
            var child = GetItemFromChildren(value, objectProperty.PropertyType);

            if (child == null)
            {
                child = (EntityBase)Activator.CreateInstance(objectProperty.PropertyType);

                FillProperty("Code", objectProperty, row, mappingColumn, child);
                FillProperty("Description", objectProperty, row, mappingColumn, child);
                FillDate("ReferenceDate", child, pathDate);
            }

            return child;
        }

        private EntityBase GetItemFromChildren(string value, Type objectProperty)
        {
            var childrenInformation = Children.FirstOrDefault(x => x.Name == objectProperty.Name);

            if (childrenInformation == null)
            {
                var repositoryBaseType = typeof(Citi.HeadCount.Domain.Repository.RepositoryBase<>);
                Type[] typeArgs = { objectProperty };
                var genericType = repositoryBaseType.MakeGenericType(typeArgs);
                var repository = Activator.CreateInstance(genericType);

                childrenInformation = new ChildrenInformation
                {
                    Name = objectProperty.Name,
                    Items = ((IList)repository.GetType().GetMethod("GetAllDistinctByCodeOrDescription").Invoke(repository, null))
                };
                Children.Add(childrenInformation);
            }

            var propertyKey = objectProperty.GetProperty("Code");
            var child = propertyKey != null ? childrenInformation.Items.Cast<EntityBase>().FirstOrDefault(x => x != null && propertyKey.GetValue(x, null).ToString() == value) : null;

            if (child == null)
            {
                propertyKey = objectProperty.GetProperty("Description");
                child = propertyKey != null ? childrenInformation.Items.Cast<EntityBase>().FirstOrDefault(x => x != null && propertyKey.GetValue(x, null).ToString() == value) : null;
            }
            return child;
        }

        private static void FillDate(string propertyName, object child, DateTime date)
        {
            var propertyKey = child.GetType().GetProperty(propertyName);
            if (propertyKey == null) return;

            propertyKey.SetValue(child, date, null);
        }

        private static void FillProperty(string propertyName, PropertyInfo objectProperty, DataRow row, ColumnMapping mappingColumn, object child)
        {
            var propertyKey = objectProperty.PropertyType.GetProperty(propertyName);
            if (propertyKey == null) return;

            var propertyInfo = mappingColumn.GetType().GetProperty(string.Concat(objectProperty.PropertyType.Name, propertyName));
            if (propertyInfo == null) return;

            var columnIndex = int.Parse(propertyInfo.GetValue(mappingColumn, null).ToString());
            if (columnIndex > -1)
                propertyKey.SetValue(child, Convert.ChangeType(row[columnIndex], propertyKey.PropertyType), null);
        }

        private static PropertyInfo GetPropertyCodeOrDescription(Type objectProperty)
        {
            var propertyKey = objectProperty.GetProperty("Code");
            if (propertyKey == null)
                propertyKey = objectProperty.GetProperty("Description");
            return propertyKey;
        }

        private static DateTime GetDateFile(string pathFile)
        {
            var pathWithExtension = pathFile.Replace(".xlsx", string.Empty).Replace(".xls", string.Empty);

            try
            {
                var date = pathWithExtension.Split(new[] { '_' }).Last();
                return new DateTime(int.Parse(date.Substring(0, 4)), int.Parse(date.Substring(4, 2)), 1);
            }
            catch (Exception)
            {
                return DateTime.MinValue;
            }
        }

        #endregion
    }
}

